package com.example.repository;

import com.example.model.Coupon;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface CouponRepository extends JpaRepository<Coupon, Long> {
    
    // 根据优惠券代码查找
    Optional<Coupon> findByCode(String code);
    
    // 查找活跃的优惠券
    Page<Coupon> findByIsActiveTrueOrderByCreatedAtDesc(Pageable pageable);
    
    // 根据类型查找优惠券
    List<Coupon> findByTypeAndIsActiveTrue(Coupon.CouponType type);
    
    // 查找可用的优惠券（活跃且在有效期内且有库存）
    @Query("SELECT c FROM Coupon c WHERE c.isActive = true " +
           "AND (c.startTime IS NULL OR c.startTime <= :now) " +
           "AND (c.endTime IS NULL OR c.endTime >= :now) " +
           "AND (c.totalQuantity = 0 OR c.usedQuantity < c.totalQuantity)")
    List<Coupon> findAvailableCoupons(LocalDateTime now);
    
    // 默认使用当前时间的可用优惠券查询
    default List<Coupon> findAvailableCoupons() {
        return findAvailableCoupons(LocalDateTime.now());
    }
    
    // 查找即将过期的优惠券
    @Query("SELECT c FROM Coupon c WHERE c.isActive = true " +
           "AND c.endTime IS NOT NULL " +
           "AND c.endTime BETWEEN :now AND :expiryDate")
    List<Coupon> findExpiringCoupons(LocalDateTime now, LocalDateTime expiryDate);
    
    // 统计活跃优惠券数量
    @Query("SELECT COUNT(c) FROM Coupon c WHERE c.isActive = true")
    Long countActiveCoupons();
    
    // 统计已使用的优惠券数量
    @Query("SELECT COALESCE(SUM(c.usedQuantity), 0) FROM Coupon c")
    Long countUsedCoupons();
    
    // 根据名称模糊查找
    @Query("SELECT c FROM Coupon c WHERE c.name LIKE %:name% AND c.isActive = true")
    List<Coupon> findByNameContainingAndIsActiveTrue(String name);
    
    // 按类型统计优惠券
    @Query("SELECT c.type, COUNT(c) FROM Coupon c WHERE c.isActive = true GROUP BY c.type")
    List<Object[]> countByType();
}
